#region NameSpace
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Collections;
using PrintDataGrid;
using System.Collections.Specialized;
#endregion

namespace eBirdAttend
{
    public partial class SecurityRoundsRpt : UserControl
    {
        public string strCon = Properties.Settings.Default["ConString"].ToString();
        SqlConnection sqlCon = new SqlConnection();
        SqlCommand sqlCmd = new SqlCommand();

        public SecurityRoundsRpt()
        {
            InitializeComponent();
            sqlCon.ConnectionString = strCon;
            sqlCmd.Connection = sqlCon;
        }

        private void btnShow_Click(object sender, EventArgs e)
        {

            try
            {
                //if (gvwEmployee.SelectedRows.Count == 0)
                //{
                //    MessageBox.Show("Select an Employee");
                //    return;

                //}
                if (gvwEmployee.SelectedRows.Count > 0)
                {
                    if (gvwEmployee.SelectedRows.Count > 1)
                    {
                        MessageBox.Show("Select a single one");
                        return;

                    }
                }
                  string EmpID =string.Empty;

                

                string strWhere = "";
                string strStartDate = dateStartDate.Value.Date.ToString("MM/dd/yyyy");
                string strEndDate = dateEndDate.Value.Date.ToString("MM/dd/yyyy");

                int dateStatus = -1;
                if (cmbRange.SelectedIndex == 0)
                {
                    MessageBox.Show("Select the date condtion");
                    cmbRange.Focus();
                    return;
                }
                else
                {
                    if (cmbRange.SelectedIndex == 1)
                    {
                        dateStatus = 2; //between
                        strWhere = "Convert(varchar,trsTimeStamp,101) between'" + strStartDate.ToString() + "' and '" + strEndDate.ToString() + "'";
                    }
                    else if (cmbRange.SelectedIndex == 2)
                    {
                        dateStatus = 3; //less than equal to
                        strWhere = "Convert(varchar,trsTimeStamp,101) <= '" + strStartDate.ToString() + "' ";
                    }
                    else if (cmbRange.SelectedIndex == 3)
                    {
                        dateStatus = 4; //greater than equal to
                        strWhere = "Convert(varchar,trsTimeStamp,101) >= '" + strStartDate.ToString() + "' ";
                    }
                }
                if (gvwEmployee.SelectedRows.Count > 0)
                {
                    EmpID = gvwEmployee.SelectedRows[0].Cells[0].Value.ToString();
                    strWhere = strWhere + " and EmpId = '" + EmpID + "'";
                }
                //strWhere = strWhere + "DATEPART(dy, Convert(varchar,trsTimeStamp,101))= DATEPART(dy, '" + strStartDate + "') ";

                //if (cmbEmployee.SelectedIndex != 0)
                //{
                //    strWhere = strWhere + " and EmpId = '" + cmbEmployee.SelectedValue +"'";
                //}

               

                if (sqlCon.State == ConnectionState.Closed )
                    sqlCon.Open();
                //sqlCmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName as Name, trsTimeStamp as Time,INOUTStr as INOUT, DepName as Department, BrName as Branch, EmpId as EmpID "
                //                      + "FROM         dbo.InoutStatusView ";
                sqlCmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName as Name,[dbo].[FormatDateTime](trsTimeStamp, 'HH:MM 12') as Time,INOUTStr as INOUT,Convert(varchar,trsTimeStamp,101) as [date] "
                                      + "FROM         dbo.InoutStatusView ";


                if (strWhere != "")
                {
                    strWhere = " where " + strWhere;
                    sqlCmd.CommandText += strWhere;
                    sqlCmd.CommandText += " order by EmpId";
                }
               
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Parameters.Clear();

                sqlCmd.ExecuteNonQuery();
                DataSet ds = new DataSet();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
                sqlAdap.Fill(ds);
               
                ////  add table to arrange data horizontally
                //int colCount1 = 0;
                //int colCount2 = 0;
                int hour = 12;
                int dsCount = 0;
                string name = string.Empty;
                string date = string.Empty;

                if (ds.Tables[0].Rows.Count > 0)
                {
                    dsCount = ds.Tables[0].Rows.Count;
                    name = ds.Tables[0].Rows[0][0].ToString();
                    date = ds.Tables[0].Rows[0][3].ToString();
                }
               DataTable dataTable = new DataTable("table1");
               DataColumn tableColumn = new DataColumn();
               DataRow tableRow;
               tableRow = dataTable.NewRow();
               dataTable.Columns.Add("Name");//Add column for displaying Employee Name
               tableRow[0] = name;

               dataTable.Columns.Add("Date"); //Add column for displaying Date
               tableRow[1] = date;

              // int column =2;
               for (int i = 0; i < 24; i++)
                   if (i < 12)
                   {
                       if (i == 0)
                           dataTable.Columns.Add("" + "12" + ":00:00 AM");
                       else
                           dataTable.Columns.Add("" + i + ":00:00 AM");
                   }
                   else if (i < 24)
                   {
                       if (i != 12)
                           hour = i - 12;
                       dataTable.Columns.Add("" + hour + ":00:00 PM");
                   }
               
               for (int i = 0; i < dsCount;i++) 
               {                   

                   if ((name != ds.Tables[0].Rows[i][0].ToString()) || (date != ds.Tables[0].Rows[i][3].ToString()))
                   {//
                       //colCount1 = 0;
                       //colCount2 = ds.Tables[0].Columns.Count - 1; // no.of columns in the existing table

                       name = ds.Tables[0].Rows[i]["Name"].ToString();
                       date = ds.Tables[0].Rows[i]["date"].ToString();
                       dataTable.Rows.Add(tableRow);
                       tableRow = dataTable.NewRow();
                       tableRow[0] = name;
                       tableRow[1] = date;                      
                       //column = 2;
                   }                  

                   //if (((colCount1 == colCount2) || (colCount2 == 0)) && (i != dsCount - 1)) //add colums if current row needs more columns than previous
                   //{
                   //    dataTable.Columns.Add("Log " + i + " ");
                   //    colCount1++;
                   //}

                   //tableRow[column] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"]; // show log time and status
                   //column++;                                    


                   DateTime logTime = DateTime.Parse(ds.Tables[0].Rows[i]["Time"].ToString());
                    // add logs to columns based on log time
                   if ((logTime >= DateTime.Parse("12:00:00 AM")) && (logTime < DateTime.Parse("01:00:00 AM")))
                       tableRow[2] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"]; 
                   if ((logTime >=DateTime.Parse("01:00:00 AM")) && (logTime < DateTime.Parse("02:00:00 AM")))
                       tableRow[3] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("02:00:00 AM")) && (logTime < DateTime.Parse("03:00:00 AM")))
                       tableRow[4] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("03:00:00 AM")) && (logTime < DateTime.Parse("04:00:00 AM")))
                       tableRow[5] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("04:00:00 AM")) && (logTime < DateTime.Parse("05:00:00 AM")))
                       tableRow[6] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("05:00:00 AM")) && (logTime < DateTime.Parse("6:00:00 AM")))
                       tableRow[7] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("6:00:00 AM")) && (logTime < DateTime.Parse("07:00:00 AM")))
                       tableRow[8] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("07:00:00 AM")) && (logTime < DateTime.Parse("08:00:00 AM")))
                       tableRow[9] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("08:00:00 AM")) && (logTime < DateTime.Parse("09:00:00 AM")))
                       tableRow[10] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("09:00:00 AM")) && (logTime < DateTime.Parse("10:00:00 AM")))
                       tableRow[11] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("10:00:00 AM")) && (logTime < DateTime.Parse("11:00:00 AM")))
                       tableRow[12] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("11:00:00 AM")) && (logTime < DateTime.Parse("12:00:00 PM")))
                       tableRow[13] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("12:00:00 PM")) && (logTime < DateTime.Parse("01:00:00 PM")))
                       tableRow[14] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("01:00:00 PM")) && (logTime < DateTime.Parse("02:00:00 PM")))
                       tableRow[15] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("02:00:00 PM")) && (logTime < DateTime.Parse("03:00:00 PM")))
                       tableRow[16] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("03:00:00 PM")) && (logTime < DateTime.Parse("04:00:00 PM")))
                       tableRow[17] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("04:00:00 PM")) && (logTime < DateTime.Parse("05:00:00 PM")))
                       tableRow[18] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("05:00:00 PM")) && (logTime < DateTime.Parse("06:00:00 PM")))
                       tableRow[19] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("06:00:00 PM")) && (logTime < DateTime.Parse("07:00:00 PM")))
                       tableRow[20] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("07:00:00 PM")) && (logTime < DateTime.Parse("08:00:00 PM")))
                       tableRow[21] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("08:00:00 PM")) && (logTime < DateTime.Parse("09:00:00 PM")))
                       tableRow[22] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("09:00:00 PM")) && (logTime < DateTime.Parse("10:00:00 PM")))
                       tableRow[23] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("10:00:00 PM")) && (logTime < DateTime.Parse("11:00:00 PM")))
                       tableRow[24] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                   if ((logTime >= DateTime.Parse("11:00:00 PM")) && (logTime < DateTime.Parse("12:00:00 AM")))
                       tableRow[25] = ds.Tables[0].Rows[i]["Time"] + " : " + ds.Tables[0].Rows[i]["INOUT"];
                                    
               }
               dataTable.Rows.Add(tableRow);
               ////

               dataGridView1.DataSource = dataTable;
               dataGridView1.DataMember = dataTable.ToString();
                dataGridView1.Columns[5].Visible = false;
                dataGridView1.Columns[0].MinimumWidth = 140;
                dataGridView1.Columns[1].MinimumWidth = 150;
                dataGridView1.Columns[2].MinimumWidth = 110;
                dataGridView1.Columns[3].MinimumWidth = 125;
                dataGridView1.Columns[4].MinimumWidth = 120;
                sqlCon.Close();
            }
            catch (Exception ex)
            {
              // MessageBox.Show(ex.Message);
            }

           
        }

        private void SecurityRoundsRpt_Load(object sender, EventArgs e)
        {

            GetDesignation();
            ShowEmployees();
            cmbRange.SelectedIndex = 0;

            //sqlCmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName as Name,EmpId from EmployDetails where EmpDesigId = 1";
            //SqlDataAdapter sqlAda = new SqlDataAdapter(sqlCmd);
            //DataSet ds=new DataSet();

            //departmentDataset.Clear();
            //sqlAda.Fill(ds);

            //DataTable table1 = new DataTable("Table1");
            //table1 = ds.Tables[0].Clone();
            //DataRow row;
            //row = table1.NewRow();
            //row["EmpId"] = 0;
            //row["Name"] = "[Select]";
            //table1.Rows.Add(row);

            //DataTable table2 = ds.Tables[0];

            //table1.Merge(table2);

            //cmbEmployee.DataSource = table1;


            //cmbEmployee.DisplayMember = "Name";
            //cmbEmployee.ValueMember = "EmpId";

            //cmbRange.SelectedIndex = 0;
            //cmbEmployee.SelectedIndex = 0;
            //cmbEmployee.Focus();

          
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void btnShowCrystalReport_Click(object sender, EventArgs e)
        {
        
        }

        private void btnPrint_Click(object sender, EventArgs e)
        {
            PrintDGV printdgv = new PrintDGV();
            printdgv.Print_DataGridView(dataGridView1);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.Rows.Count <= 0)
                {
                    MessageBox.Show("There is no items in the table to export");
                    return;
                }
                int j = 1;
                int i;
                string strReportTemplate = Application.StartupPath.ToString() + "\\" +
                    Properties.Settings.Default.SecurityRoundsRpt.ToString();

                if (strReportTemplate == "")
                {
                    MessageBox.Show("Report Template Name Missing");
                    return;
                }



                StringCollection strHeaderCol = new StringCollection();
                EbExcelAuto exelAttnReport = new EbExcelAuto();
                string curColText = "";
                exelAttnReport.Init();
                exelAttnReport.OpenExcelFile(strReportTemplate);
                exelAttnReport.FillExcellSheetFromDatagrid("#START", dataGridView1, "EmpId");
                exelAttnReport.SaveAs(null);
                exelAttnReport.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void txtFirstName_TextChanged(object sender, EventArgs e)
        {
            string strQuery = string.Empty;
            if (cmbDesignation.SelectedIndex == 0 && txtFirstName.Text.Trim() == "")
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName, Address, Phone,Mobile, Shift " +
                            " FROM         SearchEmpView where EmpStatus=1 ";
                sqlCmd.CommandText = strQuery;

            }
            else if (cmbDesignation.SelectedIndex != 0 && txtFirstName.Text.Trim() == "")
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName, Address, Phone,Mobile, Shift " +
            " FROM         SearchEmpView where EmpStatus=1 and lower(Designation) like '%" + cmbDesignation.Text.ToLower() + "'";
                sqlCmd.CommandText = strQuery;


            }
            else if (txtFirstName.Text.Trim() != "" && cmbDesignation.SelectedIndex != 0)
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName, Address, Phone,Mobile, Shift " +
                " FROM         SearchEmpView WHERE     (EmpStatus = 1) ";

                string strCondition = " and";
                strCondition += " EmpStatus=1 and lower(Designation) like '%" + cmbDesignation.Text.ToLower() + "' and lower(FirstName) like '" + txtFirstName.Text.ToLower() + "%'";
                sqlCmd.CommandText = strQuery + strCondition;

            }
            else if (txtFirstName.Text.Trim() != "" && cmbDesignation.SelectedIndex == 0)
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName, Address, Phone,Mobile, Shift " +
" FROM         SearchEmpView WHERE     (EmpStatus = 1)  ";

                string strCondition = " and";
                strCondition += " EmpStatus=1 and lower(FirstName) like '" + txtFirstName.Text.ToLower() + "%'";
                sqlCmd.CommandText = strQuery + strCondition;

            }

            else
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName, Address, Phone,Mobile, Shift " +
            " FROM         SearchEmpView ";

                sqlCmd.CommandText = strQuery;

            }
            sqlCmd.Connection = sqlCon;
            if (sqlCon.State == ConnectionState.Open)
                sqlCon.Close();
            sqlCon.Open();
            SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
            sqlCmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            sqlAdap.Fill(ds);
            sqlCon.Close();
            if (ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("SecurityName" +"  " + txtFirstName.Text +" "+ "is not found");
                txtFirstName.Focus();
                return;
            }
            else
            {
                gvwEmployee.DataSource = ds;
                gvwEmployee.DataMember = ds.Tables[0].ToString();
            }

            
        }


        public void ShowEmployees()
        {
            string strQuery = "";
            if (cmbDesignation.SelectedIndex != 0)
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName,Designation, Address, Phone,Mobile, Shift  " +
                           " FROM         SearchEmpView  where EmpStatus=1 and lower(Designation) like '%" + cmbDesignation.Text.ToLower() + "'";

            }
            else
            {
                strQuery = "SELECT EmpId,EmpCode as EmployeeCode, isnull(FirstName,'')+''+ isnull(LastName,'') as EmployeeName,Designation, Address, Phone,Mobile, Shift  " +
           " FROM         SearchEmpView  where EmpStatus=1";
            }

            sqlCmd.CommandText = strQuery;
            sqlCmd.Connection = sqlCon;
            if (sqlCon.State== ConnectionState.Open)
                sqlCon.Close();

            sqlCon.Open();
            SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
            sqlCmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            sqlAdap.Fill(ds);
            sqlCon.Close();

            gvwEmployee.DataSource = ds;
            gvwEmployee.DataMember = ds.Tables[0].ToString();

            gvwEmployee.Columns[0].MinimumWidth = 30;
            gvwEmployee.Columns[1].MinimumWidth = 100;
            gvwEmployee.Columns[2].MinimumWidth = 150;
            //gvwEmployee.Columns[3].MinimumWidth = 150;
            //gvwEmployee.Columns[4].MinimumWidth = 200;
            //gvwEmployee.Columns[5].MinimumWidth = 250;

            gvwEmployee.Columns[0].Visible = false;

            // gvwEmployee.Font.Name= "Verdana";
            // gvwEmployee.Font.Size = 8;

            //sqlCmd.CommandText = "select * from SearchEmpView";
            //DataSet ds = new DataSet();
            //SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
            //sqlAdap.Fill(ds);
            //gvwEmployee.DataSource = ds.DefaultViewManager;
        }

        private void label4_Click(object sender, EventArgs e)
        {
           
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        protected void GetDesignation()
        {
            sqlCmd.CommandText = "SELECT     DesId, DesDesig FROM         Designation";
            SqlDataAdapter sqlAda = new SqlDataAdapter(sqlCmd);
            DataSet ds=new DataSet();

            departmentDataset.Clear();
            sqlAda.Fill(ds);

            DataTable table1 = new DataTable("Table1");
            table1 = ds.Tables[0].Clone();
            DataRow row;
            row = table1.NewRow();
            row["DesId"] = 0;
            row["DesDesig"] = "[Select]";
            table1.Rows.Add(row);

            DataTable table2 = ds.Tables[0];

            table1.Merge(table2);

            cmbDesignation.DataSource = table1;


            cmbDesignation.DisplayMember = "DesDesig";
            cmbDesignation.ValueMember = "DesId";

            cmbDesignation.SelectedIndex = 0;
            cmbDesignation.Focus();
         
        }

        private void cmbDesignation_SelectedIndexChanged(object sender, EventArgs e)
        {
            ShowEmployees();
        }
        
    }
}
